﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;

namespace CashDifferenceReportApp
{
    public static class DBHelper
    {
        private static string mainCS = "Initial Catalog=MainBilling;Data Source=182.18.135.133; User ID=vijay;Password=Ishanvi@123;Connection Timeout=5000;";
        private static string salariesCS = "Initial Catalog=KSSalaries;Data Source=182.18.135.133;User ID=vijay;Password=Ishanvi@123";

        internal static DataTable GetBranches()
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataTable ds = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT BranchID, BranchName FROM MasterBranches", mainCS);
                adapter.Fill(ds);
                if (ds != null)
                    return ds;
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetFinancialReport(DateTime dateTime, string branchID)
        {
            SqlConnection con = null;
            try
            {
                StringBuilder str = new StringBuilder();

                //{2}
                string BILLTABLE = "Bill";

                //{3}
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dateTime.Month && DateTime.Now.Year == dateTime.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dateTime.ToString("MMM"), dateTime.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dateTime.ToString("MMM"), dateTime.Year.ToString());
                }

                //POS Sales - 0
                //str.Append("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN Bill B ON B.BillNo = BD.BillNo WHERE DateDiff(dd, BD.Date, '{0}') = 0  AND B.IsBillCancelled = 0  AND B.IsSpecialBill = 0 ");
                str.Append("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM {3} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN {2} B ON B.BillNo = BD.BillNo WHERE DateDiff(dd, BD.Date, '{0}') = 0 AND DateDiff(dd, B.BillDate, '{0}') = 0 AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND BD.Branch = {1} AND B.Branch = {1} ");
                str.Append(" GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");

                //POS Total Sales & POS Discounts - 1
                str.Append("SELECT ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM {2} WHERE DateDiff(dd, {2}.BillDate, '{0}') = 0 AND IsBillCancelled = 0  AND IsSpecialBill = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Cake Advances & Cake Discounts - 2
                str.Append("SELECT ROUND(SUM(Advance + DiscountByCost), 2) AS Advance, ROUND(SUM(DiscountByCost),2) AS CakeDiscounts FROM Cake WHERE IsCancelled = 0 AND DateDiff(dd, BookedDate, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Cake Balances - 3
                str.Append("SELECT ROUND(SUM(Balance), 2) AS Balance FROM Cake WHERE IsCancelled = 0 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0)) ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Expenses - 4
                str.Append("SELECT ROUND(SUM(Amount),2) AS ExpenseAmount FROM Expense WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Sodexho & Creditcard Sales - 5
                str.Append("SELECT SUM(SodexhoAmount) AS SodexhoAmount, SUM(CreditCardAmount) AS CreditCardAmount FROM Amounts WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Credit Note Amount - 6
                str.Append("SELECT ROUND(SUM(Amount),2) AS CreditNoteAmount FROM CreditNote CN JOIN {2} B ON CN.BillNo = B.BillNo WHERE DateDiff(DD, CN.Date, '{0}') = 0 AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND CN.Branch = {1} AND B.Branch = {1};");

                //Vendor Bills Amount - 7
                str.Append("SELECT ROUND(SUM(Amount),2) AS VendorSales FROM VendorSales VS JOIN {2} B ON B.BillNo = VS.BillNo WHERE DateDiff(DD, VS.Date, '{0}') = 0 AND DateDiff(DD, B.BillDate, '{0}') = 0 AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND VS.Branch = {1} AND B.Branch = {1};");

                //Bulk Orders Amount - 8
                str.Append(" SELECT SUM(Advance) AS BulkOrderAdvance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND DateDiff(DD, BO.BookedDate, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {1};");

                //Bulk Orders Balance - 9
                str.Append(" SELECT SUM(Balance) AS BulkOrderBalance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND ((BO.DeliveredDate IS NULL AND DATEDIFF(DD, BO.DeliveryDate, '{0}') = 0) OR (BO.DeliveredDate IS NOT NULL AND DATEDIFF(DD, BO.DeliveredDate, '{0}') = 0)) ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {1};");

                //Cashier Wise POS Sales and POS Discounts - 10 - TODO-UNCOMMENT THE BELOW QUERY AFTER CASHIER ID WISE SALES IMPLEMENTATION
                str.Append("SELECT B.[USER] AS ID, E.EmpName AS Cashier, ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM {2} B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE DateDiff(dd, B.BillDate, '{0}') = 0 AND IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND B.Branch = {1}");
                str.Append(" GROUP BY B.[USER], E.EmpName;");
                /*str.Append("SELECT B.[USER] AS ID, B.[User] AS Cashier, ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM {2} B WHERE DateDiff(dd, B.BillDate, '{0}') = 0 AND IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND B.Branch = {1}");
                str.Append(" GROUP BY B.[USER];");*/

                //Cashier Wise Expenses - 11
                str.Append("SELECT Cashier, ROUND(SUM(Amount),2) AS ExpenseAmount FROM Expense WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //Cashier Wise Sodexho & Credit Card Amounts - 12
                str.Append("SELECT Cashier, SUM(SodexhoAmount) AS SodexhoAmount, SUM(CreditCardAmount) AS CreditCardAmount FROM Amounts WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //Cake Advances By Cashier - 13
                str.Append("SELECT Cashier, ROUND(SUM(Advance), 2) AS Advance, ROUND(SUM(DiscountByCost),2) AS CakeDiscounts FROM Cake WHERE IsCancelled = 0 AND DateDiff(dd, BookedDate, '{0}') = 0");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //Actual Cake Balances - 14
                str.Append("SELECT CashierDelivered AS Cashier, ROUND(SUM(Balance), 2) AS CakeBalances FROM Cake WHERE IsCancelled = 0 AND CashierDelivered <> 0 AND DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY CashierDelivered;");

                //BulkOrder Advances By Cashier - 15
                str.Append("SELECT Cashier, ROUND(SUM(Advance), 2) AS Advance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND DateDiff(dd, BO.BookedDate, '{0}') = 0");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //BulkOrder Balances - 16
                str.Append("SELECT CashierDelivered AS Cashier, ROUND(SUM(Balance), 2) AS Balance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND BO.IsOrderDelivered = 1 AND DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY CashierDelivered;");
                
                con = new SqlConnection(mainCS);
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format(str.ToString(), dateTime.ToString("MM/dd/yyyy"), branchID, BILLTABLE, BILLDETAILTABLE);
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception) { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetEmpAdvancesandBills(DateTime dateTime, string branchID)
        {
            SqlConnection con = new SqlConnection(salariesCS);
            try
            {
                string query = "SELECT ROUND(SUM(Amount),2) AS EmployeeDebitAmount FROM DebitTransactions WHERE DATEDIFF(DD, AddedDate, '{0}') = 0 ";
                if (branchID != "0")
                    query += " AND FromBranch = {1} ";
                query += " AND TransactionType IN (1,2);";

                //Cashier Wise Debit Transactions
                query += "SELECT Cashier, ROUND(SUM(Amount),2) AS EmployeeDebitAmount FROM DebitTransactions WHERE DATEDIFF(DD, AddedDate, '{0}') = 0 ";
                if (branchID != "0")
                    query += " AND FromBranch = {1} ";
                query += " AND TransactionType IN (1,2) GROUP BY Cashier;";

                using (SqlCommand cmd = new SqlCommand(string.Format(query, dateTime.ToString("MM/dd/yyyy"), branchID), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        //    return double.Parse(ds.Tables[0].Rows[0]["EmployeeDebitAmount"].ToString());
                        return ds;
                    }
                }
            }
            catch (Exception) { }
            return null;
        }

        internal static DataRow GetActualAmount(DateTime date, string branch)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT ActualCashAvailable, Difference FROM AvailableCash WHERE DateDiff(dd, Date, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch), mainCS);
                adapter.Fill(ds, "Branches");
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    return ds.Tables[0].Rows[0];
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetActuals(DateTime date, string branch, DataTable dt)
        {
            try
            {
                DataTable ds = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT Type, ActualCashAvailable, -1 * ROUND(Difference, 2) AS Difference FROM AvailableCash WHERE DateDiff(dd, Date, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch), mainCS);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    return ds;
            }
            catch (Exception)
            { }
            return null;
        }

        internal static int SaveActuals(DateTime date, string branch, DataGridView dgvIndividualSales)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;

            try
            {
                con = new SqlConnection(mainCS);

                if (con.State == ConnectionState.Closed)
                    con.Open();
                trans = con.BeginTransaction();

                foreach (DataGridViewRow row in dgvIndividualSales.Rows)
                {
                    double cashAvailable = 0d;
                    double.TryParse(row.Cells["CashAvailable"].Value.ToString(), out cashAvailable);

                    double cashInHand = 0d;
                    double.TryParse(row.Cells["CashInHand"].Value.ToString(), out cashInHand);

                    using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrModifyAvailableCash '{0}', '{1}', '{2}', '{3}', '{4}', '{5}'", date.ToString("MM/dd/yyyy"), branch, row.Cells["Cashier"].Value.ToString(), cashAvailable, cashInHand, cashAvailable - cashInHand), con, trans))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                return 1;
            }
            catch (Exception)
            {
                trans.Rollback();
                return 0;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        internal static DataTable GetCancelledBills(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;
            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                IsException = false;
                string str;
                con = new SqlConnection(mainCS);

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, [User] AS Cashier, (CONVERT(VARCHAR, BillDate, 106) + SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Date, CancelledReason, BillDate FROM {3} WHERE IsBillCancelled = 1 AND Branch = {0} AND MONTH(BillDate) = {1} AND YEAR(BillDate) = {2}", branch, date.Month, date.Year, BILLTABLE);
                }
                else
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, [User] AS Cashier, (SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Time, CancelledReason, BillDate FROM {2} WHERE IsBillCancelled = 1 AND DATEDIFF(DD, BillDate, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch, BILLTABLE);
                }

                using (SqlDataAdapter adapter = new SqlDataAdapter(str, con))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetBillDetailsByBillNo(string billNo, string branch, DateTime date, out bool IsException)
        {
            SqlConnection con = null;

            try
            {
                IsException = false;
                con = new SqlConnection(mainCS); 
                
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                using (SqlCommand cmd = new SqlCommand("SELECT BillNo, Item AS ItemCode, MI.ItemName, BD.Qty FROM " + BILLDETAILTABLE + " BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE DateDiff(dd, BD.Date, '" + date.ToString("MM/dd/yyyy") + "') = 0 AND BillNo = '" + billNo + "'", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetDiscountedBills(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;
            IsException = false;
            try
            {
                string str;
                con = new SqlConnection(mainCS);

                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, L.UserName AS Cashier, (CONVERT(VARCHAR, BillDate, 106) + SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Date, DiscountReason, BillDate FROM {3} B JOIN Login L ON B.[User] = L.EmpID WHERE (DiscountByPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {0} AND MONTH(BillDate) = {1} AND YEAR(BillDate) = {2}", branch, date.Month, date.Year, BILLTABLE);
                }
                else
                {
                    //str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, [User] AS Cashier, (SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Time, DiscountReason, BillDate FROM {2} WHERE (DiscountByPercentage <> 0 OR DiscountByCost <> 0) AND DATEDIFF(DD, BillDate, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch, BILLTABLE);
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, L.UserName AS Cashier, (SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Time, DiscountReason, BillDate FROM {2} B JOIN Login L ON B.[User] = L.EmpID WHERE (DiscountByPercentage <> 0 OR DiscountByCost <> 0) AND DATEDIFF(DD, BillDate, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch, BILLTABLE);
                }

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (ds != null && ds.Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetCakesReport(string branch, DateTime date, bool forWholeMonth, string category, out bool IsException)
        {
            SqlConnection con = null;
            IsException = false;
            try
            {
                string str = string.Empty;
                con = new SqlConnection(mainCS);

                if (forWholeMonth)
                {
                    if (category == "General")
                    //if (!isCancelledCakes)
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }
                    else if (category == "Cancelled")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }
                    else if (category == "Undelivered")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }
                    else if (category == "Discounted")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }

                    str = string.Format(str, branch, date.Month, date.Year);
                }
                else
                {
                    //if (!isCancelledCakes)
                    if (category == "General")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND IsCakeDelivered = 1 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 1 AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    else if (category == "Cancelled")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    else if (category == "Undelivered")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    else if (category == "Discounted")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, C.Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    str = string.Format(str, date.ToString("MM-dd-yyyy"), branch);
                }

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetCakesDeliveryReport(string branch, DateTime date, out bool IsException)
        {
            SqlConnection con = null;
            IsException = false;
            try
            {
                string str;
                con = new SqlConnection(mainCS);

                //str = string.Format("SELECT C.BillNo, C.CakeNo, C.ID, C.Advance, C.Balance, CONVERT(VARCHAR, C.BookedDate, 105) AS BookedDate, CONVERT(VARCHAR, C.DeliveryDate, 105) AS DeliveryDate, CONVERT(VARCHAR, C.DeliveredDate, 105) AS DeliveredDate FROM Cake C WHERE C.IsCancelled = 0 AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))", date.ToString("MM/dd/yyyy"), branch);
                str = string.Format("SELECT C.BillNo, C.CakeNo, C.ID, C.Advance, C.Balance, CONVERT(VARCHAR, C.BookedDate, 105) AS BookedDate, CONVERT(VARCHAR, C.DeliveryDate, 105) AS DeliveryDate, CONVERT(VARCHAR, C.DeliveredDate, 105) AS DeliveredDate FROM Cake C WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0; SELECT C.BillNo, C.CakeNo, C.ID, C.Advance, C.Balance, CONVERT(VARCHAR, C.BookedDate, 105) AS BookedDate, CONVERT(VARCHAR, C.DeliveryDate, 105) AS DeliveryDate, CONVERT(VARCHAR, C.DeliveredDate, 105) AS DeliveredDate FROM Cake C WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0;", date.ToString("MM/dd/yyyy"), branch);
                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetBulkOrders(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;

            try
            {
                IsException = false;

                string str;

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, B.PhoneNo, TotalAmount, DiscountByCost AS 'Disc(Rs)', DiscountByPercentage AS 'Disc(%)', CONVERT(VARCHAR, BookedDate, 106) AS BookedDate, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, IsOrderCancelled AS Cancelled, CancelledReason AS Reason, E.EmpName AS Cashier, BookedDate FROM BulkOrder B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE B.Branch = {0} AND ((MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2}) OR (MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}))", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT BillNo, B.PhoneNo, TotalAmount, DiscountByCost AS 'Disc(Rs)', DiscountByPercentage AS 'Disc(%)', CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, IsOrderCancelled AS Cancelled, CancelledReason AS Reason, E.EmpName AS Cashier, BookedDate FROM BulkOrder B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE ((DATEDIFF(DD, BookedDate, '{0}') = 0) OR (DATEDIFF(DD, DeliveryDate, '{0}') = 0)) AND B.Branch = {1}", date.ToString("MM/dd/yyyy"), branch);
                }

                con = new SqlConnection(mainCS);

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (ds != null && ds.Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            return null;
        }

        internal static bool ValidateVersion(int appID, int version)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT ApplicationID FROM VersionManagement WHERE ApplicationID = {0} AND Version = {1}", appID, version), mainCS);
                adapter.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception)
            { }
            return false;
        }
    }
}
